﻿/* Copyright (c) 2010 Lexalytics Inc.

 Permission is hereby granted, free of charge, to any person
 obtaining a copy of this software and associated documentation
 files (the "Software"), to deal in the Software without
 restriction, including without limitation the rights to use,
 copy, modify, merge, publish, distribute, sublicense, and/or sell
 copies of the Software, and to permit persons to whom the
 Software is furnished to do so, subject to the following
 conditions:

 The above copyright notice and this permission notice shall be
 included in all copies or substantial portions of the Software.

 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 OTHER DEALINGS IN THE SOFTWARE.
*/

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Resources;
using System.Windows.Forms;
using System.Text;
using System.Xml;

// Needed for Office and Excel integration
using Excel = Microsoft.Office.Interop.Excel;

// Needed for business classes
using LexaScopeNET;
using Lexalytics4Excel.Dialogs;
using Lexalytics4Excel.SheetHelpers;
using Lexalytics;

namespace Lexalytics4Excel
{
    /// <summary>
    /// Main controller class for LexaScope projects
    /// </summary>
    public class DataProject
    {
        #region Member data
        // Settings information
        private Profiles _Profiles = new Profiles();
        private string _CurrentProfile = "";
        private int _TopN = 10;

        private bool _bRefreshResults = false;
        private bool _bWriteLog = false;

        // Operational information
        private LXAWebService wsLXA = new LXAWebService();

        /// <summary>Access to worksheet associated with project</summary>
        public string UserRangeWorksheet = "";
        /// <summary>Access to string depicting Excel range selected</summary>
        public string UserRangeSelection = "";
        /// <summary>Access to identifier of column containing full text</summary>
        public string UserRangeTextColumn = "";
        /// <summary>Access to index of column containing full text</summary>
        public int UserRangeTextColumnIndex = 0;
        /// <summary>Access to indicator of range including a header column</summary>
        public bool UserRangeIncludesHeader = false;

        public decimal SentimentNeutralLower = -0.5m;
        public decimal SentimentNeutralUpper = 0.5m;

        // Persistence-level information
        private string _ProjectWSName = "LXAProject";
        private int _DataHeaderRow = 1;
        private Hashtable _DataHashtable = new Hashtable();

        // Project-level information contained in columns A and B
        private string _ProjectNameCol = "A";
        private string _ProjectNameHeader = "Name";
        private string _ProjectValCol = "B";
        private string _ProjectValHeader = "Value";
        private string _ProjectKey = "";

        /// <summary>In-memory list of all results for project</summary>
        private Database.SqlHelper _db;
        public Database.SqlHelper LocalDatabase { get {return _db;}}

        /// <summary>Public property providing project name</summary>
        public string ProjectName {get {return _ProjectKey;} }

        /// <summary>Property indicating if data has been selected for project</summary>
        public bool HasData { get { return (!String.IsNullOrEmpty(UserRangeSelection)); } }
        private bool _MultiColumn = false;
        /// <summary>Property indicating if multiple columns exist in selected data range</summary>
        public bool MultiColumn { get { return _MultiColumn; } }
        /// <summary>Public property access to log flag</summary>
        public bool WriteLog { get { return _bWriteLog; } }

        /// <summary>
        /// Collection of themes in data
        /// </summary>
        public SortedList<int, LXATheme> ThemeResults = new SortedList<int, LXATheme>();
        /// <summary>
        /// Collection of people entities in data
        /// </summary>
        public SortedList<int, LXAEntity> PeopleResults = new SortedList<int, LXAEntity>();
        /// <summary>
        /// Collection of company entities in data
        /// </summary>
        public SortedList<int, LXAEntity> CompanyResults = new SortedList<int, LXAEntity>();
        /// <summary>
        /// Collection of place entities in data
        /// </summary>
        public SortedList<int, LXAEntity> PlaceResults = new SortedList<int, LXAEntity>();
        /// <summary>
        /// Collection of product entities in data
        /// </summary>
        public SortedList<int, LXAEntity> ProductResults = new SortedList<int, LXAEntity>();

        /// <summary>
        /// Type of text analytics analysis being performed
        /// </summary>
        public enum AnalysisType
        {
            /// <summary>Get top N results</summary>
            TopN = 1,
            /// <summary>Get results filtered by specific value in specified column</summary>
            Filtered = 2,
            /// <summary>Get results sorted by unique values in specified column</summary>
            Sorted = 4
        }

        #endregion

        /// <summary>
        /// Default constructor. Checks for hidden worksheet for persistence of data project
        /// </summary>
        public DataProject()
        {
            // Check for unpacked EULA
            if (!PerformEulaCheck())
                return;

            // Get persisted application settings
            if (Lexalytics4Excel.Properties.Settings.Default["Top_N"] != null)
                _TopN = Convert.ToInt32(Lexalytics4Excel.Properties.Settings.Default["Top_N"]);
            if (Lexalytics4Excel.Properties.Settings.Default["WriteLog"] != null)
                _bWriteLog = Convert.ToBoolean(Lexalytics4Excel.Properties.Settings.Default["WriteLog"]);
            if (Lexalytics4Excel.Properties.Settings.Default["NeutralUpper"] != null)
                SentimentNeutralUpper = Convert.ToDecimal(Lexalytics4Excel.Properties.Settings.Default["NeutralUpper"]);
            if (Lexalytics4Excel.Properties.Settings.Default["NeutralLower"] != null)
                SentimentNeutralLower = Convert.ToDecimal(Lexalytics4Excel.Properties.Settings.Default["NeutralLower"]);

            if (CheckForCredentials())
            {
                // Check for persisted project information
                if (CheckForProjectWorksheet())
                {
                    _ProjectKey = GetProjectInfo("project_key");
                    _CurrentProfile = GetProjectInfo("project_profile");
                    UserRangeWorksheet = GetProjectInfo("data_sheet");
                    UserRangeSelection = GetProjectInfo("data_range");
                    UserRangeTextColumn = GetProjectInfo("text_column");
                    if (!String.IsNullOrEmpty(GetProjectInfo("text_column_index")))
                        UserRangeTextColumnIndex = Convert.ToInt32(GetProjectInfo("text_column_index"));
                    else
                        UserRangeTextColumnIndex = 1;
                    _MultiColumn = (GetProjectInfo("multi_column").ToLower() == "true");
                    UserRangeIncludesHeader = (GetProjectInfo("includes_header").ToLower() == "true");
                }
            }
        }

        /// <summary>
        /// Handles the Excel range selected by user
        /// </summary>
        /// <param name="DataRange">The selected range of cells in Excel</param>
        /// <exception cref="System.Exception">Thrown if exception occurs in process of selecting data and adding to project</exception>
        /// <remarks>
        /// If one row selected, sort warning dialog
        /// else if one column selected, confirmation dialog with number of rows and columns, including checkbox asking if range includes header
        /// else confirmation dialog with indicator requiring selection of text column, including checkbox asking if range includes header
        /// </remarks>
        public void SelectData(Excel.Range DataRange)
        {
            bool bConfirmDataSelect = false;

            try
            {
                // Check for unpacked EULA
                if (!PerformEulaCheck())
                    return;

                // Make sure we have mandatory info
                if (_Profiles == null && _Profiles.Count<1)
                {
                    MessageBox.Show("Profile parameters must be set before analyzing data.","Lexalytics Configuration", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
                    DoOptions(true);
                    return;
                }

                // Determine if this is the first time data has been selected
                if (String.IsNullOrEmpty(_ProjectKey) || (String.IsNullOrEmpty(_CurrentProfile)))
                {
                    // Get information from user about project name to use and profile to use
                    Dialogs.NewProject dlgNewProject = new NewProject(_Profiles, _CurrentProfile);
                    if (dlgNewProject.ShowDialog() == DialogResult.OK)
                    {
                        _CurrentProfile = dlgNewProject.SelectedProfile;
                        if (!CreateProject(dlgNewProject.ProjectName))
                        {
                            MessageBox.Show("An error occurred while trying to set up your project.", "Lexalytics4Excel", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                            return;
                        }
                        SetProjectInfo("project_profile", _CurrentProfile);
                    }
                    else
                    {
                        return;
                    }
                }

                // Confirm the user's selection of single row
                if (DataRange.Rows.Count == 1)
                {
                    DialogResult WarningChoice = MessageBox.Show("You have only selected a single row of data to add. Would you like to expand this selection to all rows in the same columns?\n\nPress Cancel to quit data selection.", "Selection Warning", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                    switch (WarningChoice)
                    {
                        case DialogResult.Yes:
                            DataRange = DataRange.EntireColumn;
                            DataRange.Select();
                            bConfirmDataSelect = true;
                            break;
                        case DialogResult.No:
                            bConfirmDataSelect = true;
                            break;
                    }
                }
                else
                {
                    bConfirmDataSelect = true;
                }

                // Get information about the selection
                if (bConfirmDataSelect)
                {
                    Dialogs.SelectData dlgSelectData = new SelectData(DataRange);
                    if (dlgSelectData.ShowDialog() == DialogResult.OK)
                    {
                        WriteToLog("Beginning data processing for " + _ProjectKey);
                        // Process the data and persist selection
                        ProcessDataInThread(DataRange, dlgSelectData.SelectedDataHasHeader, dlgSelectData.TextColumnIndex);
                    }
                }
            }
            catch (Exception err)
            {
                Exception procErr = new Exception("An error occurred while adding documents to the project.", err);
                WriteToLog(procErr);
                throw procErr;
            }
            return;
        }

        #region Text Analytics ribbon events
        /// <summary>
        /// Displays the ProjectSettings dialog, either for creating or editing a project
        /// </summary>
        /// <param name="ProjectName">User-provided project name</param>
        /// <exception cref="System.Exception">Thrown if exception occurs in process of creating the project</exception>
        public bool CreateProject(string ProjectName)
        {
            bool bRet = false;

            try
            {
                // If the project worksheet doesn't exist, we're starting from 
                // scratch and need to create a project ID. Once we have a project ID
                // we can create the project worksheet, persist the project settings.
                // The project will be created when an asset is added to it.
                if (!CheckForProjectWorksheet())
                    CreateProjectWorksheet();

                _ProjectKey = ProjectName;
                int infoRow = SetProjectInfo("project_key", _ProjectKey);
                // Create the cache database and set refresh results to false
                // This will prevent the cache database from being recreated unnecessarily when our next step is to process data
                // But the cache database should be recreated if we reopen the spreadsheet and select data
                ConnectCacheDB(true);
                _bRefreshResults = false;
                bRet = true;
            }
            catch (Exception err)
            {
                Exception procErr = new Exception("An error occurred while creating the project.", err);
                WriteToLog(procErr);
                throw procErr;
            }

            return bRet;
        }

        /// <summary>
        /// Execute an update of the local results cache
        /// </summary>
        public void DoUpdate()
        {
            int nRet = -1;
            _bRefreshResults = true;
            ConnectCacheDB(_bRefreshResults);
            if (_bRefreshResults)
            {
                // Refresh the local cache
                nRet = WSUpdateResults();
                if (nRet == 0)
                    _bRefreshResults = false;
            }
        }

        /// <summary>
        /// Displays options dialog, updates settings if Options dialog OK'd
        /// </summary>
        public void DoOptions(bool InitialSettings)
        {
            Dialogs.Options myOptionsDialog = new Dialogs.Options(InitialSettings);             
            if (myOptionsDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                _Profiles = new Profiles((StringCollection)Lexalytics4Excel.Properties.Settings.Default["Profiles"]);
                _TopN = Convert.ToInt32(Lexalytics4Excel.Properties.Settings.Default["Top_N"]);
                SentimentNeutralUpper = Convert.ToDecimal(Lexalytics4Excel.Properties.Settings.Default["NeutralUpper"]);
                SentimentNeutralLower = Convert.ToDecimal(Lexalytics4Excel.Properties.Settings.Default["NeutralLower"]);
                _bWriteLog = Convert.ToBoolean(Lexalytics4Excel.Properties.Settings.Default["WriteLog"]);
            }
        }

        /// <summary>
        /// Displays the About box
        /// </summary>
        public void DoAbout()
        {
            Dialogs.About myAboutBox = new Dialogs.About();
            myAboutBox.ShowDialog();
        }

        /// <summary>
        /// Calls web service to get themes, passes to sheet helper object for rendering results.
        /// </summary>
        /// <param name="ResultType">Integer indicating type of results view to produce</param>
        /// <exception cref="System.Exception">Thrown if exception occurs in process of getting themes and displaying them</exception>
        /// <remarks>
        /// When adding Top N results, no need to add column/value information.
        /// When adding filtered results, want to indicate in header the column and value 
        /// information. But individual results do not need additional info. When adding 
        /// sorted results, want to indicate in header the column used to sort. And when 
        /// adding each section of results, want to indicate the column value for the current 
        /// section.
        /// </remarks>
        public void GetThemes(int ResultType)
        {
            int nRet = -1;
            try
            {
                ConnectCacheDB(_bRefreshResults);
                if (_bRefreshResults)
                {
                    // Refresh the local cache
                    nRet = SalienceUpdate();
                    if (nRet == 0)
                        _bRefreshResults = false;
                }

                if (ResultType == (int)AnalysisType.Sorted)
                {
                    Dialogs.SortDialog dlgSort = new SortDialog(this);
                    if (dlgSort.ShowDialog() == DialogResult.OK)
                    {
                        // Use first value in list of values to generate the results sheet, then add to it.
                        int nValueIndex = 1;
                        ThemeHelper myThemes = null;
                        foreach (string FilterValue in dlgSort.ColumnValues)
                        {
                            nRet = GetFilteredThemes(dlgSort.FilterColumn, FilterValue, _bRefreshResults);
                            if (nRet == 0)
                            {
                                if (nValueIndex == 1)
                                {
                                    myThemes = new ThemeHelper(this);
                                    if (myThemes.GenerateResultsSheet(dlgSort.FilterColumn) == 0)
                                        myThemes.GenerateResultsSection(FilterValue);
                                    else
                                        return;
                                }
                                else
                                {
                                    // Add subsequent section of sorted results
                                    myThemes.GenerateResultsSection(FilterValue);
                                }
                            }
                            nValueIndex++;
                        }
                    }
                }
                else if (ResultType == (int)AnalysisType.Filtered)
                {
                    Dialogs.FilterDialog dlgFilter = new FilterDialog(this);
                    if (dlgFilter.ShowDialog() == DialogResult.OK)
                    {
                        nRet = GetFilteredThemes(dlgFilter.FilterColumn, dlgFilter.FilterValue, _bRefreshResults);
                        if (nRet == 0)
                        {
                            ThemeHelper myThemes = new ThemeHelper(this);
                            if (myThemes.GenerateResultsSheet(dlgFilter.FilterColumn, dlgFilter.FilterValue) == 0)
                                myThemes.GenerateResultsSection();
                        }
                    }
                }
                else
                {
                    nRet = GetAllThemes(_bRefreshResults);
                    if (nRet == 0)
                    {
                        ThemeHelper myThemes = new ThemeHelper(this);
                        if (myThemes.GenerateResultsSheet() == 0)
                            myThemes.GenerateResultsSection();
                    }
                }
            }
            catch (Exception err)
            {
                Exception procErr = new Exception("An error occurred while getting themes.", err);
                WriteToLog(procErr);
                throw procErr;
            }
        }

        /// <summary>
        /// Calls web service to get entity extraction results, passes to sheet helper object for rendering results.
        /// </summary>
        /// <param name="ResultType">Integer indicating type of results view to produce</param>
        /// <param name="EntityType">Integer indicating type of entity to extract</param>
        /// <exception cref="System.Exception">Thrown if exception occurs in process of getting entities and displaying them</exception>
        /// <remarks>
        /// When adding Top N results, no need to add column/value information.
        /// When adding filtered results, want to indicate in header the column and value 
        /// information. But individual results do not need additional info. When adding 
        /// sorted results, want to indicate in header the column used to sort. And when 
        /// adding each section of results, want to indicate the column value for the current 
        /// section.
        /// </remarks>
        public void GetEntities(int ResultType, int EntityType)
        {
            int nRet = -1;
            try
            {
                ConnectCacheDB(_bRefreshResults);
                if (_bRefreshResults)
                {
                    // Refresh the local cache
                    nRet = SalienceUpdate();
                    if (nRet == 0)
                        _bRefreshResults = false;
                }

                if (ResultType == (int)AnalysisType.Sorted)
                {
                    Dialogs.SortDialog dlgSort = new SortDialog(this);
                    if (dlgSort.ShowDialog() == DialogResult.OK)
                    {
                        // Use first value in list of values to generate the results sheet, then add to it.
                        int nValueIndex = 1;
                        EntityHelper myEntities = null;
                        foreach (string FilterValue in dlgSort.ColumnValues)
                        {
                            nRet = GetFilteredEntities(dlgSort.FilterColumn, FilterValue, EntityType, _bRefreshResults);
                            if (nRet == 0)
                            {
                                if (nValueIndex == 1)
                                {
                                    myEntities = new EntityHelper(this, EntityType);
                                    if (myEntities.GenerateResultsSheet(dlgSort.FilterColumn) == 0)
                                    {
                                        // Add first section of sorted results
                                        myEntities.GenerateResultsSection(FilterValue);
                                    }
                                    else
                                    {
                                        return;
                                    }
                                }
                                else
                                {
                                    // Add subsequent section of sorted results
                                    myEntities.GenerateResultsSection(FilterValue);
                                }
                            }
                            nValueIndex++;
                        }
                    }
                }
                else if (ResultType == (int)AnalysisType.Filtered)
                {
                    Dialogs.FilterDialog dlgFilter = new FilterDialog(this);
                    if (dlgFilter.ShowDialog() == DialogResult.OK)
                    {
                        nRet = GetFilteredEntities(dlgFilter.FilterColumn, dlgFilter.FilterValue, EntityType, _bRefreshResults);
                        if (nRet == 0)
                        {
                            EntityHelper myEntities = new EntityHelper(this, EntityType);
                            if (myEntities.GenerateResultsSheet(dlgFilter.FilterColumn, dlgFilter.FilterValue)==0)
                                myEntities.GenerateResultsSection();
                        }
                    }
                }
                else
                {
                    nRet = GetAllEntities(EntityType, _bRefreshResults);
                    if (nRet == 0)
                    {
                        EntityHelper myEntities = new EntityHelper(this, EntityType);
                        if (myEntities.GenerateResultsSheet()==0)
                            myEntities.GenerateResultsSection();
                    }
                }
            }
            catch (Exception err)
            {
                Exception procErr = new Exception("An error occurred while getting entities.", err);
                WriteToLog(procErr);
                throw procErr;
            }
        }

        #endregion // Text Analytics ribbon events

        #region Private Utility Methods
        private bool PerformEulaCheck()
        {
            bool bReturn = false;

            ResourceManager rm = Lexalytics4Excel.Properties.Resources.ResourceManager;
            string internalEula = rm.GetString("EULA");
            string internalChecksum = GetChecksum(internalEula);

            string userFilePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
            string eulaPath = userFilePath + "/Lexalytics/Lexalytics4Excel/";
            string eulaFile = eulaPath + "eula";
            // If we find a EULA, check that it is ours.
            if (System.IO.File.Exists(eulaFile))
            {
                string localEula = System.IO.File.ReadAllText(eulaFile);
                string localChecksum = GetChecksum(localEula);

                if (localChecksum.CompareTo(internalChecksum) == 0)
                    bReturn = true;
            }

            // If there is no EULA, or it does not match the internal EULA, display for user acceptance
            if (!bReturn)
            {
                Dialogs.LicenseAgreement dlgEula = new LicenseAgreement();
                dlgEula.LicenseText = internalEula;
                if (dlgEula.ShowDialog() == DialogResult.OK)
                {
                    System.IO.Directory.CreateDirectory(eulaPath);
                    System.IO.StreamWriter sw = System.IO.File.CreateText(eulaFile);
                    sw.Write(internalEula);
                    sw.Close();
                    bReturn = true;
                }
            }

            return bReturn;
        }

        private string GetChecksum(string InputString)
        {
            string Checksum = "";
            byte[] textBytes = System.Text.Encoding.Default.GetBytes(InputString);
            try
            {
                System.Security.Cryptography.MD5CryptoServiceProvider cryptHandler;
                cryptHandler = new System.Security.Cryptography.MD5CryptoServiceProvider();
                byte[] hash = cryptHandler.ComputeHash(textBytes);
                string outputStr = "";
                foreach (byte a in hash)
                {
                    if (a < 16)
                        outputStr += "0" + a.ToString("x");
                    else
                        outputStr += a.ToString("x");
                }
                Checksum = outputStr;
            }
            catch
            {
                Checksum = "";
            }
            return Checksum;
        }

        /// <summary>
        /// Determines if we have profiles stored in settings file
        /// </summary>
        /// <returns></returns>
        private bool CheckForCredentials()
        {
            bool bCredentialsFound = false;

            if (Lexalytics4Excel.Properties.Settings.Default["Profiles"] != null)
                _Profiles = new Profiles((StringCollection)Lexalytics4Excel.Properties.Settings.Default["Profiles"]);

            // If mandatory information not provided, display Options dialog
            if (_Profiles==null || _Profiles.Count<1)
            {
                MessageBox.Show("At least one profile must be created before the Lexalytics add-in can be used for analyzing data.", "Lexalytics Configuration", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
                DoOptions(true);
            }
            else
            {
                bCredentialsFound = true;
            }
                
            return bCredentialsFound;
        }

        /// <summary>
        /// Checks for the existence of the project worksheet
        /// </summary>
        /// <returns>True/False indicating project worksheet exists</returns>
        private bool CheckForProjectWorksheet()
        {
            bool bProjectWorksheetExists = false;
            foreach (Excel.Worksheet ws in Globals.LexalyticsAddIn.Application.Sheets)
            {
                if (ws.Name.Equals(_ProjectWSName))
                {
                    bProjectWorksheetExists = true;
                    break;
                }
            }
            return bProjectWorksheetExists;
        }

        /// <summary>
        /// Creates the project worksheet for persisting project data
        /// </summary>
        /// <exception cref="System.Exception">Thrown if exception occurs in creating project worksheet</exception>
        private void CreateProjectWorksheet()
        {
            try
            {
                // Retain name of current worksheet so we can return to it
                Excel.Worksheet currentWS = (Excel.Worksheet)Globals.LexalyticsAddIn.Application.ActiveSheet;
                string currentWSName = currentWS.Name;

                Excel.Worksheet projectWS = (Excel.Worksheet)Globals.LexalyticsAddIn.Application.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Excel.XlSheetType.xlWorksheet);
                projectWS.Name = _ProjectWSName;
                projectWS.Visible = Excel.XlSheetVisibility.xlSheetHidden;

                // Column A and B are project-level
                Excel.Range r = (Excel.Range)projectWS.Cells[_DataHeaderRow, _ProjectNameCol];
                r.Value2 = _ProjectNameHeader;
                r = (Excel.Range)projectWS.Cells[_DataHeaderRow, _ProjectValCol];
                r.Value2 = _ProjectValHeader;

                // Switch back to original worksheet
                foreach (Excel.Worksheet ws in Globals.LexalyticsAddIn.Application.Sheets)
                {
                    if (ws.Name.Equals(currentWSName))
                    {
                        ((Excel._Worksheet)ws).Activate();
                        break;
                    }
                }
            }
            catch (Exception err)
            {
                Exception procErr = new Exception("An error occurred while creating internal storage for project information.", err);
                WriteToLog(procErr);
                throw procErr;
            }
        }

        /// <summary>
        /// Returns a value from persisted project-level information
        /// </summary>
        /// <param name="InfoKey">String identifying the value</param>
        /// <exception cref="System.Exception">Thrown if exception occurs in getting project information</exception>
        /// <returns>String value for </returns>
        private string GetProjectInfo(string InfoKey)
        {
            string infoValue = "";
            int keyRow = _DataHeaderRow;

            try
            {
                Excel.Range keyRange = (Excel.Range)((Excel.Worksheet)Globals.LexalyticsAddIn.Application.Sheets[_ProjectWSName]).Cells[keyRow, _ProjectNameCol];

                while ((keyRange.Value2 != null) && (infoValue.Length == 0))
                {
                    if (keyRange.Value2.ToString().CompareTo(InfoKey) == 0)
                    {
                        Excel.Range valueRange = (Excel.Range)((Excel.Worksheet)Globals.LexalyticsAddIn.Application.Sheets[_ProjectWSName]).Cells[keyRow, _ProjectValCol];
                        infoValue = valueRange.Value2.ToString();
                    }
                    else
                    {
                        keyRow++;
                        keyRange = (Excel.Range)((Excel.Worksheet)Globals.LexalyticsAddIn.Application.Sheets[_ProjectWSName]).Cells[keyRow, _ProjectNameCol];
                    }
                }
            }
            catch (Exception err)
            {
                Exception procErr = new Exception("An error occurred while reading information about the project.", err);
                WriteToLog(procErr);
                throw procErr;
            }
            return infoValue;
        }

        /// <summary>
        /// Persists a piece of project-level information
        /// </summary>
        /// <param name="InfoKey">Key for the information</param>
        /// <param name="InfoValue">Value to associate with key</param>
        /// <exception cref="System.Exception">Thrown if exception occurs in storage of project info</exception>
        private int SetProjectInfo(string InfoKey, string InfoValue)
        {
            int keyRow = _DataHeaderRow;
            int valueRow = 0;

            try
            {
                Excel.Range keyRange = (Excel.Range)((Excel.Worksheet)Globals.LexalyticsAddIn.Application.Sheets[_ProjectWSName]).Cells[keyRow, _ProjectNameCol];

                while ((keyRange.Value2 != null) && (valueRow == 0))
                {
                    if (keyRange.Value2.ToString().CompareTo(InfoKey) == 0)
                    {
                        Excel.Range valueRange = (Excel.Range)((Excel.Worksheet)Globals.LexalyticsAddIn.Application.Sheets[_ProjectWSName]).Cells[keyRow, _ProjectValCol];
                        valueRange.Value2 = InfoValue;
                        valueRow = keyRow;
                    }
                    else
                    {
                        keyRow++;
                        keyRange = (Excel.Range)((Excel.Worksheet)Globals.LexalyticsAddIn.Application.Sheets[_ProjectWSName]).Cells[keyRow, _ProjectNameCol];
                    }
                }

                if (valueRow == 0)
                {
                    keyRange.Value2 = InfoKey;
                    Excel.Range valueRange = (Excel.Range)((Excel.Worksheet)Globals.LexalyticsAddIn.Application.Sheets[_ProjectWSName]).Cells[keyRow, _ProjectValCol];
                    valueRange.Value2 = InfoValue;
                    valueRow = keyRow;
                }
            }
            catch (Exception err)
            {
                Exception procErr = new Exception("An error occurred while storing information about the project.", err);
                WriteToLog(procErr);
                throw procErr;
            }
            return valueRow;
        }

        /// <summary>
        /// Performs the internal work of getting content from selected range, creating the hashes, and calling web service for new items
        /// </summary>
        /// <param name="DataRange">User-specified range</param>
        /// <param name="IncludesHeader">Indicator of header row</param>
        /// <param name="TextColumnIndex">Index of column selected as full-text</param>
        private void ProcessDataInThread(Excel.Range DataRange, bool IncludesHeader, int TextColumnIndex)
        {
            Cursor tempCursor = System.Windows.Forms.Cursor.Current;
            try
            {
                // Invoke progress dialog to perform work in thread
                Profile myProfile = _Profiles[_CurrentProfile];

                // Check the cache database
                ConnectCacheDB(_bRefreshResults);         

                Dialogs.ProcessLocalDataProgress dlgProgress = new ProcessLocalDataProgress(myProfile, _DataHashtable, DataRange, IncludesHeader, TextColumnIndex, _bWriteLog, _db);
                dlgProgress.ShowDialog();

                // Persist the hashes back to storage
                Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
                _DataHashtable = dlgProgress.DataHashes;

                // Persist information about range to storage
                UserRangeWorksheet = DataRange.Worksheet.Name;
                UserRangeSelection = DataRange.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
                Excel.Range _rng = (Excel.Range)DataRange.Cells[1, TextColumnIndex];
                string strRange_A1 = _rng.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
                string[] arrBounds = strRange_A1.Split(new char[] { '$', ':' }, StringSplitOptions.RemoveEmptyEntries);
                switch (arrBounds.GetLength(0))
                {
                    case 4:
                        UserRangeTextColumn = arrBounds[2];
                        break;
                    default:
                        UserRangeTextColumn = arrBounds[0];
                        break;
                }
                UserRangeIncludesHeader = IncludesHeader;
                UserRangeTextColumnIndex = TextColumnIndex;
                _MultiColumn = dlgProgress.MultiColumnDetected;

                SetProjectInfo("data_sheet", UserRangeWorksheet);
                SetProjectInfo("data_range", UserRangeSelection);
                SetProjectInfo("text_column", UserRangeTextColumn);
                SetProjectInfo("text_column_index", UserRangeTextColumnIndex.ToString());
                if (UserRangeIncludesHeader)
                    SetProjectInfo("includes_header", "true");
                else
                    SetProjectInfo("includes_header", "false");
                if (_MultiColumn)
                    SetProjectInfo("multi_column", "true");
                else
                    SetProjectInfo("multi_column", "false");

                // Flag for results update
                _bRefreshResults = false;
            }
            catch (Exception err)
            {
                Cursor.Current = tempCursor;
                Exception procErr = new Exception("An error occurred while processing the content.", err);
                WriteToLog(procErr);
                throw procErr;
            }
            finally
            {
                Cursor.Current = tempCursor;
            }
        }

        /// <summary>
        /// Retrieves top N entities from cache database, updating if needed
        /// </summary>
        /// <param name="nEntityType">Type of entity to retrieve</param>
        /// <param name="bRefreshCache">Flag to determine if cache should be updated</param>
        /// <returns>Integer status code</returns>
        private int GetAllEntities(int nEntityType, bool bRefreshCache)
        {
            int nStatus = 0;
            string sqlQuery = "";
            string strEntityType = "";

            switch (nEntityType)
            {
                case (int)LXAEntityType.Types.Company:
                    strEntityType = "Company";
                    break;
                case (int)LXAEntityType.Types.Person:
                    strEntityType = "Person";
                    break;
                case (int)LXAEntityType.Types.Place:
                    strEntityType = "Place";
                    break;
                case (int)LXAEntityType.Types.Product:
                    strEntityType = "Product";
                    break;
                default:
                    strEntityType = "Custom";
                    break;
            }

            try
            {
                // Update local cache of all results
                if (bRefreshCache)
                    nStatus = SalienceUpdate();

                // Get the top N entities
                SortedList<int, LXAEntity> EntityResults = new SortedList<int, LXAEntity>();
                sqlQuery = String.Format("SELECT entity, count(entity) from entities where type=\"{0}\" group by entity order by count(entity) desc", strEntityType);
                _db.execute(sqlQuery);
                int nResults = 0;
                while (_db.nextRow())
                {
                    if (nResults < _TopN)
                    {
                        LXAEntity aEntity = new LXAEntity() { Entity = _db.getString(0) };
                        EntityResults.Add(nResults, aEntity);
                        nResults++;
                    }
                    else
                        break;
                }
                // For each entity, get entity information
                foreach (LXAEntity aEntity in EntityResults.Values)
                {
                    int nMentions = 0;
                    sqlQuery = String.Format("SELECT identifier, magnitude, mentions from entities where type=\"{0}\" and entity=\"" + aEntity.Entity + "\"", strEntityType);
                    _db.execute(sqlQuery);
                    while (_db.nextRow())
                    {
                        LXAEntityMention aEntityMention = new LXAEntityMention() { Document = _db.getString(0), Magnitude = Convert.ToDouble(_db.getFloat(1)), Mentions = _db.getInt(2) };
                        aEntityMention.Sentiment = 0;
                        if (Convert.ToDecimal(aEntityMention.Magnitude) >= this.SentimentNeutralUpper)
                            aEntityMention.Sentiment = 1;
                        if (Convert.ToDecimal(aEntityMention.Magnitude) <= this.SentimentNeutralLower)
                            aEntityMention.Sentiment = -1;
                        aEntity.MentionList.Add(aEntityMention);
                        nMentions += _db.getInt(2);
                    }
                    aEntity.Mentions = nMentions;
                    // Get mention-level information for all mentions of this entity
                    foreach (LXAEntityMention aEntityMention in aEntity.MentionList)
                    {
                        sqlQuery = "SELECT label,value from metadata where identifier=\"" + aEntityMention.Document + "\"";
                        _db.execute(sqlQuery);
                        while (_db.nextRow())
                        {
                            if (!aEntityMention.EntityMetadata.ContainsKey(_db.getString(0)))
                                aEntityMention.EntityMetadata.Add(_db.getString(0), _db.getString(1));
                        }
                    }
                }

                // Set the type-specific entity results list
                switch (nEntityType)
                {
                    case (int)LXAEntityType.Types.Company:
                        CompanyResults.Clear();
                        CompanyResults = EntityResults;
                        break;
                    case (int)LXAEntityType.Types.Person:
                        PeopleResults.Clear();
                        PeopleResults = EntityResults;
                        break;
                    case (int)LXAEntityType.Types.Place:
                        PlaceResults.Clear();
                        PlaceResults = EntityResults;
                        break;
                    case (int)LXAEntityType.Types.Product:
                        ProductResults.Clear();
                        ProductResults = EntityResults;
                        break;
                }
            }
            catch (Exception err)
            {
                string strMessage = String.Format("An exception occurred in call to web service method to get {0} entities.", strEntityType);
                Exception wsErr = new Exception(strMessage, err);
                WriteToLog(wsErr);
                throw wsErr;
            }
            return nStatus;
        }

        /// <summary>
        /// Retrieves top N entities meeting filter criteria from cache database, updating if needed
        /// </summary>
        /// <param name="FilterLabel">Label of metadata to use for filtering</param>
        /// <param name="FilterValue">Value of metadata field</param>
        /// <param name="nEntityType">Type of entity to retrieve</param>
        /// <param name="bRefreshCache">Flag to determine if cache should be updated</param>
        /// <returns>Integer status code</returns>
        private int GetFilteredEntities(string FilterLabel, string FilterValue, int nEntityType, bool bRefreshCache)
        {
            int nStatus = 0;
            string sqlQuery = "";
            string strEntityType = "";

            switch (nEntityType)
            {
                case (int)LXAEntityType.Types.Company:
                    strEntityType = "Company";
                    break;
                case (int)LXAEntityType.Types.Person:
                    strEntityType = "Person";
                    break;
                case (int)LXAEntityType.Types.Place:
                    strEntityType = "Place";
                    break;
                case (int)LXAEntityType.Types.Product:
                    strEntityType = "Product";
                    break;
                default:
                    strEntityType = "Custom";
                    break;
            }

            try
            {
                // Update local cache of all results
                if (bRefreshCache)
                    nStatus = SalienceUpdate(); ;

                // Get the top N entities
                SortedList<int, LXAEntity> EntityResults = new SortedList<int, LXAEntity>();
                sqlQuery = "SELECT e.entity, count(e.entity) from entities e, metadata m where e.identifier=m.identifier and ";
                sqlQuery += String.Format("e.type=\"{0}\" and ", strEntityType);
                sqlQuery += String.Format("m.label=\"{0}\" and m.value=\"{1}\" ", FilterLabel, FilterValue);
                sqlQuery += "group by e.entity order by count(e.entity) desc";
                _db.execute(sqlQuery);
                int nResults = 0;
                while (_db.nextRow())
                {
                    if (nResults < _TopN)
                    {
                        LXAEntity aEntity = new LXAEntity() { Entity = _db.getString(0) };
                        EntityResults.Add(nResults, aEntity);
                        nResults++;
                    }
                    else
                        break;
                }
                // For each entity, get entity information
                foreach (LXAEntity aEntity in EntityResults.Values)
                {
                    int nMentions = 0;
                    sqlQuery = String.Format("SELECT e.identifier, e.magnitude, e.mentions from entities e, metadata m where e.type=\"{0}\" and e.entity=\"{1}\"", strEntityType, aEntity.Entity);
                    sqlQuery += " and e.identifier=m.identifier";
                    sqlQuery += String.Format(" and m.label=\"{0}\" and m.value=\"{1}\" ", FilterLabel, FilterValue);
                    _db.execute(sqlQuery);
                    while (_db.nextRow())
                    {
                        LXAEntityMention aEntityMention = new LXAEntityMention() { Document = _db.getString(0), Magnitude = Convert.ToDouble(_db.getFloat(1)), Mentions = _db.getInt(2) };
                        aEntityMention.Sentiment = 0;
                        if (Convert.ToDecimal(aEntityMention.Magnitude) >= this.SentimentNeutralUpper)
                            aEntityMention.Sentiment = 1;
                        if (Convert.ToDecimal(aEntityMention.Magnitude) <= this.SentimentNeutralLower)
                            aEntityMention.Sentiment = -1;
                        aEntity.MentionList.Add(aEntityMention);
                        nMentions += _db.getInt(2);
                    }
                    aEntity.Mentions = nMentions;
                    // Get mention-level information for all mentions of this entity
                    foreach (LXAEntityMention aEntityMention in aEntity.MentionList)
                    {
                        sqlQuery = "SELECT label,value from metadata where identifier=\"" + aEntityMention.Document + "\"";
                        _db.execute(sqlQuery);
                        while (_db.nextRow())
                        {
                            if (!aEntityMention.EntityMetadata.ContainsKey(_db.getString(0)))
                                aEntityMention.EntityMetadata.Add(_db.getString(0), _db.getString(1));
                        }
                    }
                }

                // Set the type-specific entity results list
                switch (nEntityType)
                {
                    case (int)LXAEntityType.Types.Company:
                        CompanyResults.Clear();
                        CompanyResults = EntityResults;
                        break;
                    case (int)LXAEntityType.Types.Person:
                        PeopleResults.Clear();
                        PeopleResults = EntityResults;
                        break;
                    case (int)LXAEntityType.Types.Place:
                        PlaceResults.Clear();
                        PlaceResults = EntityResults;
                        break;
                    case (int)LXAEntityType.Types.Product:
                        ProductResults.Clear();
                        ProductResults = EntityResults;
                        break;
                }
            }
            catch (Exception err)
            {
                string strMessage = String.Format("An exception occurred in call to web service method to get {0} entities.", strEntityType);
                Exception wsErr = new Exception(strMessage, err);
                WriteToLog(wsErr);
                throw wsErr;
            }
            return nStatus;
        }

        /// <summary>
        /// Retrieves top N themes from cache database, updating if needed
        /// </summary>
        /// <param name="bRefreshCache">Flag to determine if cache should be updated</param>
        /// <returns>Integer status code</returns>
        private int GetAllThemes(bool bRefreshCache)
        {
            int nStatus = 0;
            string sqlQuery = "";

            try
            {
                // Update local cache of all results
                if (bRefreshCache)
                    nStatus = SalienceUpdate();

                // Clear current in-memory list
                ThemeResults.Clear();

                // Get the top N themes
                sqlQuery = "SELECT theme, count(theme) from themes group by theme order by count(theme) desc";
                _db.execute(sqlQuery);
                int nResults = 0;
                while (_db.nextRow())
                {
                    if (nResults < _TopN)
                    {
                        LXATheme aTheme = new LXATheme() { ThemeText = _db.getString(0), Mentions = _db.getInt(1) };
                        ThemeResults.Add(nResults, aTheme);
                        nResults++;
                    }
                    else
                        break;
                }
                // For each theme, get theme information
                foreach (LXATheme aTheme in ThemeResults.Values)
                {
                    sqlQuery = "SELECT identifier, score, type, sentiment, magnitude from themes where theme=\"" + aTheme.ThemeText+ "\"";
                    _db.execute(sqlQuery);
                    while (_db.nextRow())
                    {
                        LXAThemeMention aThemeMention = new LXAThemeMention() { Document = _db.getString(0), Score = Convert.ToDouble(_db.getFloat(1)), Type=_db.getInt(2), Sentiment=_db.getInt(3), Magnitude=Convert.ToDouble(_db.getFloat(4)) };
                        aTheme.MentionList.Add(aThemeMention);
                    }
                    // Get mention-level information for all occurrences of this theme
                    foreach (LXAThemeMention aThemeMention in aTheme.MentionList)
                    {
                        sqlQuery = "SELECT label,value from metadata where identifier=\"" + aThemeMention.Document + "\"";
                        _db.execute(sqlQuery);
                        while (_db.nextRow())
                        {
                            if (!aThemeMention.ThemeMetadata.ContainsKey(_db.getString(0)))
                                aThemeMention.ThemeMetadata.Add(_db.getString(0), _db.getString(1));
                        }
                    }
                }
            }
            catch (Exception err)
            {
                string strMessage = "An exception occurred in call to get themes.";
                Exception wsErr = new Exception(strMessage, err);
                WriteToLog(wsErr);
                throw wsErr;
            }
            return nStatus;
        }

        /// <summary>
        /// Retrieves top N themes meeting filter criteria from cache database, updating if needed
        /// </summary>
        /// <param name="FilterLabel">Label of metadata to use for filtering</param>
        /// <param name="FilterValue">Value of metadata field</param>
        /// <param name="bRefreshCache">Flag to determine if cache should be updated</param>
        /// <returns>Integer status code</returns>
        private int GetFilteredThemes(string FilterLabel, string FilterValue, bool bRefreshCache)
        {
            int nStatus = 0;
            string sqlQuery = "";

            try
            {
                // Update local cache of all results
                if (bRefreshCache)
                    nStatus = SalienceUpdate();

                // Clear in-memory list of theme results
                ThemeResults.Clear();

                // Get the top N themes
                sqlQuery = "SELECT t.theme, count(t.theme) from themes t, metadata m where t.identifier=m.identifier and ";
                sqlQuery += String.Format("m.label=\"{0}\" and m.value=\"{1}\" ", FilterLabel, FilterValue);
                sqlQuery += "group by t.theme order by count(t.theme) desc";
                _db.execute(sqlQuery);
                int nResults = 0;
                while (_db.nextRow())
                {
                    if (nResults < _TopN)
                    {
                        LXATheme aTheme = new LXATheme() { ThemeText = _db.getString(0), Mentions = _db.getInt(1) };
                        ThemeResults.Add(nResults, aTheme);
                        nResults++;
                    }
                    else
                        break;
                }
                // For each theme, get theme information
                foreach (LXATheme aTheme in ThemeResults.Values)
                {
                    sqlQuery = String.Format("SELECT t.identifier, t.score, t.type, t.sentiment, t.magnitude from themes t, metadata m where t.theme=\"{0}\"", aTheme.ThemeText);
                    sqlQuery += " and t.identifier=m.identifier";
                    sqlQuery += String.Format(" and m.label=\"{0}\" and m.value=\"{1}\" ", FilterLabel, FilterValue);
                    _db.execute(sqlQuery);
                    while (_db.nextRow())
                    {
                        LXAThemeMention aThemeMention = new LXAThemeMention() { Document = _db.getString(0), Score = Convert.ToDouble(_db.getFloat(1))};
                        aTheme.MentionList.Add(aThemeMention);
                    }
                    // Get mention-level information for all mentions of this theme
                    foreach (LXAThemeMention aThemeMention in aTheme.MentionList)
                    {
                        sqlQuery = "SELECT label,value from metadata where identifier=\"" + aThemeMention.Document + "\"";
                        _db.execute(sqlQuery);
                        while (_db.nextRow())
                        {
                            if (!aThemeMention.ThemeMetadata.ContainsKey(_db.getString(0)))
                                aThemeMention.ThemeMetadata.Add(_db.getString(0), _db.getString(1));
                        }
                    }
                }
            }
            catch (Exception err)
            {
                string strMessage = "An exception occurred in call to get themes.";
                Exception wsErr = new Exception(strMessage, err);
                WriteToLog(wsErr);
                throw wsErr;
            }
            return nStatus;
        }

        private void ConnectCacheDB(bool bCreate)
        {
            try
            {
                // Create the SQLite database for the project
                string userFilePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
                string dbPath = userFilePath + "/Lexalytics/Lexalytics4Excel/projects/";
                System.IO.Directory.CreateDirectory(dbPath);
                _db = new Lexalytics4Excel.Database.SqlHelper(dbPath + _ProjectKey + ".db", Database.SqlCode.newDBquery, new Database.SqlHelper.UpdateDBEvent(Database.SqlCode.updateDB), bCreate);
                if (bCreate)
                {
                    foreach (string sqlCmd in Database.SqlCode.newDBquery)
                    {
                        _db.execute(sqlCmd);
                        if (WriteLog)
                            WriteToLog(String.Format("Executing SQLite command: {0}", sqlCmd));
                    }
                }
            }
            catch (Exception err)
            {
                WriteToLog(err);
                MessageBox.Show("Error connecting to cache database:\n" + err.Message + "\n" + err.StackTrace, "Lexalytics4Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        #endregion // Private Utility Methods

        #region Logging Methods
        /// <summary>
        /// Outputs exception to log file
        /// </summary>
        /// <param name="LogError">Exception to log</param>
        public void WriteToLog(Exception LogError)
        {
            if (_bWriteLog)
                Logger.Instance.WriteToLog(LogError);
        }

        /// <summary>
        /// Outputs message string to log file
        /// </summary>
        /// <param name="LogMessage">String to log</param>
        public void WriteToLog(string LogMessage)
        {
            if (_bWriteLog)
                Logger.Instance.WriteToLog(LogMessage);
        }
        #endregion

        #region Private interface to local Salience Engine
        /// <summary>
        /// Updates local cache DB with all Salience results for project from local SalienceEngine.
        /// </summary>
        /// <returns>Integer status code</returns>
        private int SalienceUpdate()
        {
            int nStatus = 0;
            try
            {
                Profile myProfile = _Profiles[_CurrentProfile];
                Excel.Worksheet ws = (Excel.Worksheet)Globals.LexalyticsAddIn.Application.Worksheets[this.UserRangeWorksheet];
                Excel.Range dataRange = ws.get_Range(this.UserRangeSelection, Type.Missing);

                Dialogs.ProcessLocalDataProgress dlgProgress = new ProcessLocalDataProgress(myProfile, _DataHashtable, dataRange, this.UserRangeIncludesHeader, this.UserRangeTextColumnIndex, _bWriteLog, _db);
                dlgProgress.ShowDialog();

            }
            catch (Exception err)
            {
                if (WriteLog)
                    WriteToLog(err);
            }
            return nStatus;
        }


        #endregion
        
        #region Private interface to web service methods

        /// <summary>
        /// Updates local cache DB with all results for project from web service.
        /// </summary>
        /// <returns>Integer status code</returns>
        private int WSUpdateResults()
        {
            int nStatus = 0;
            try
            {
                Profile myProfile = _Profiles[_CurrentProfile];
                List<LexaScopeNET.Zephyr.Result> _ResultsList = new List<LexaScopeNET.Zephyr.Result>();
                nStatus = wsLXA.WSAllResults(myProfile.ServiceURL, myProfile.PublicKey, myProfile.PrivateKey, _ProjectKey, ref _ResultsList);
                // If results have changed, put the results into local SQLite DB

                // Clear existing tables
                _db.execute("DELETE FROM metadata");
                _db.execute("DELETE FROM themes");
                _db.execute("DELETE FROM entities");
                _db.execute("DELETE FROM asset");

                foreach (LexaScopeNET.Zephyr.Result aResult in _ResultsList)
                {
                    string strIdentifier = aResult.Identifier;
                    _db.execute(String.Format("INSERT INTO asset (identifier, sentiment) VALUES ('{0}',{1})", strIdentifier, aResult.Sentiment));
                    foreach (LexaScopeNET.Zephyr.Entity aEntity in aResult.AssetEntities)
                    {
                        _db.execute(String.Format("INSERT INTO entities (identifier, entity, type, label, sentiment, magnitude, mentions) VALUES ('{0}','{1}','{2}','{3}',{4},{5},{6})", strIdentifier, _db.makeSafe(aEntity.Text), aEntity.Type, aEntity.Label, aEntity.Sentiment, aEntity.Magnitude, aEntity.Mentions));
                    }
                    foreach (LexaScopeNET.Zephyr.Theme aTheme in aResult.AssetThemes)
                    {
                        _db.execute(String.Format("INSERT INTO themes (identifier, theme, type, score, sentiment, magnitude) VALUES ('{0}','{1}',{2},{3},{4},{5})", strIdentifier, _db.makeSafe(aTheme.Text), aTheme.Type, aTheme.Score, aTheme.Sentiment, aTheme.Magnitude));
                    }
                    foreach (LexaScopeNET.Zephyr.Metadata aMetadata in aResult.AssetMetadata)
                    {
                        _db.execute(String.Format("INSERT INTO metadata (identifier, label, value) VALUES ('{0}','{1}','{2}')", strIdentifier, aMetadata.Label, _db.makeSafe(aMetadata.Value)));
                    }
                }
            }
            catch (Exception err)
            {
                Exception wsErr = new Exception("An exception occurred in call to web service method to get results.", err);
                WriteToLog(wsErr);
                throw wsErr;
            }
            return nStatus;
        }

        #endregion // Private interface to web service methods

        /// <summary>
        /// Basic use of MD5 as hashcode for content
        /// </summary>
        /// <param name="InputStr">Input string</param>
        /// <returns>Hashcode for content signature</returns>
        private string GetDataHash(string InputStr)
        {
            byte[] textBytes = System.Text.Encoding.Default.GetBytes(InputStr);
            try
            {
                System.Security.Cryptography.MD5CryptoServiceProvider cryptHandler;
                cryptHandler = new System.Security.Cryptography.MD5CryptoServiceProvider();
                byte[] hash = cryptHandler.ComputeHash(textBytes);
                string outputStr = "";
                foreach (byte a in hash)
                {
                    if (a < 16)
                        outputStr += "0" + a.ToString("x");
                    else
                        outputStr += a.ToString("x");
                }
                return outputStr;
            }
            catch
            {
                throw;
            }

        }

    }
}
